﻿using Microsoft;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
using System.Dynamic;

namespace Loogn.DataAccess
{
    /// <summary>
    /// 数据访问基类
    /// </summary>
    /// <typeparam name="TModel"></typeparam>
    public abstract class BaseDAL<TModel>
    {
        #region private protected

        private string _connectionString;

        /// <summary>
        /// 根据GetConnectionString方法获得连接字符串
        /// </summary>
        /// <returns></returns>
        protected string ConnectionString()
        {
            if (string.IsNullOrEmpty(_connectionString))
            {
                _connectionString = GetConnectionString();
            }
            return _connectionString;
        }

        /// <summary>
        /// 根据ConnectionString创建SqlConnection对象
        /// </summary>
        /// <returns></returns>
        protected SqlConnection NewConnection()
        {
            return new SqlConnection(ConnectionString());
        }

        /// <summary>
        /// 子类实现提供连接字符串
        /// </summary>
        /// <returns></returns>
        protected abstract string GetConnectionString();

        /// <summary>
        /// 从SqlDataReader映射到TCustomerModel类型的对象
        /// </summary>
        /// <typeparam name="TCustomerModel"></typeparam>
        /// <param name="reader"></param>
        /// <returns></returns>
        protected TCustomerModel ReaderToModel<TCustomerModel>(SqlDataReader reader)
        {
            TCustomerModel model = Activator.CreateInstance<TCustomerModel>();
            var type = model.GetType();
            var props = type.GetProperties_Cache(BindingFlags.Instance | BindingFlags.Public);

            for (int i = 0; i < reader.FieldCount; i++)
            {
                var prop = props.FirstOrDefault(p => p.Name.Equals(reader.GetName(i), StringComparison.OrdinalIgnoreCase));
                if (null != prop)
                {
                    var value = reader.GetValue(i);
                    if (null == value || value is DBNull)
                        prop.SetValue(model, null, null);
                    else
                        prop.SetValue(model, value, null);
                }
            }
            return model;
        }

        /// <summary>
        /// 从SqlDataReader映射到TCustomerModel类型的集合对象
        /// </summary>
        /// <typeparam name="TCustomerModel"></typeparam>
        /// <param name="reader"></param>
        /// <returns></returns>
        protected List<TCustomerModel> ReaderToModelList<TCustomerModel>(SqlDataReader reader)
        {
            List<TCustomerModel> list = new List<TCustomerModel>();
            while (reader.Read())
            {
                var info = ReaderToModel<TCustomerModel>(reader);
                list.Add(info);
            }
            return list;
        }

        /// <summary>
        /// 从SqlDataReader映射到TModel类型的对象
        /// </summary>
        /// <param name="reader"></param>
        /// <returns></returns>
        protected virtual TModel ReaderToModel(SqlDataReader reader)
        {
            return ReaderToModel<TModel>(reader);
        }

        private List<TModel> ReaderToModelList(SqlDataReader reader)
        {
            return ReaderToModelList<TModel>(reader);
        }

        private dynamic ReaderToDynamic(SqlDataReader reader)
        {
            dynamic obj = new ExpandoObject();
            var dict = obj as IDictionary<string, object>;
            for (int i = 0; i < reader.FieldCount; i++)
            {
                dict.Add(reader.GetName(i), reader.GetValue(i));
            }
            return obj;
        }

        private List<dynamic> ReaderToDynamicList(SqlDataReader reader)
        {
            List<dynamic> list = new List<dynamic>();
            while (reader.Read())
            {
                var info = ReaderToDynamic(reader);
                list.Add(info);
            }
            return list;
        }

        private void ReaderToJson(SqlDataReader reader, StringBuilder result)
        {
            result.Append("{");
            for (int i = 0; i < reader.FieldCount; i++)
            {
                var val = reader.GetValue(i);
                if (val is DBNull)
                {
                    result.AppendFormat("\"{0}\":null", reader.GetName(i));
                }
                else
                {
                    var type = val.GetType();
                    if (type == typeof(DateTime) || type == typeof(string))
                    {
                        result.AppendFormat("\"{0}\":\"{1}\"", reader.GetName(i), val.ToString());
                    }
                    else if (type == typeof(bool))
                    {
                        result.AppendFormat("\"{0}\":{1}", reader.GetName(i), true.Equals(val) ? "true" : "false");
                    }
                    else
                    {
                        result.AppendFormat("\"{0}\":{1}", reader.GetName(i), val.ToString());
                    }
                }

                if (i < reader.FieldCount - 1)
                {
                    result.Append(",");
                }
            }
            result.Append("}");
        }

        private StringBuilder ReaderToJson(SqlDataReader reader)
        {
            StringBuilder json = new StringBuilder();
            while (reader.Read())
            {
                ReaderToJson(reader, json);
                json.Append(",");
            }
            if (json.Length == 0)
            {
                json.Append("[]");
            }
            else
            {
                json.Remove(json.Length - 1, 1);
                json.Insert(0, "[");
                json.Append("]");
            }
            return json;
        }

        #endregion

        #region ExecuteNonQuery

        /// <summary>
        ///  执行一个SQL命令，返回影响行数
        /// </summary>
        /// <param name="commandType">命令类型</param>
        /// <param name="commandText">命令文本</param>
        /// <returns></returns>
        protected int ExecuteNonQuery(CommandType commandType, string commandText)
        {
            return SqlHelper.ExecuteNonQuery(ConnectionString(), commandType, commandText);
        }

        /// <summary>
        ///  执行一个SQL命令，返回影响行数
        /// </summary>
        /// <param name="commandType">命令类型</param>
        /// <param name="commandText">命令文本</param>
        /// <param name="commandParameters">命令参数列表</param>
        /// <returns></returns>
        protected int ExecuteNonQuery(CommandType commandType, string commandText, params SqlParameter[] commandParameters)
        {
            return SqlHelper.ExecuteNonQuery(ConnectionString(), commandType, commandText, commandParameters);
        }

        /// <summary>
        /// 执行一个SQL存储过程，返回影响行数
        /// </summary>
        /// <param name="spName">存储过程</param>
        /// <param name="parameterValues">存储过程的参数值类表。注：是参数值列表，顺序和定义时的一样</param>
        /// <returns></returns>
        protected int ExecuteNonQuery(string spName, params object[] parameterValues)
        {
            return SqlHelper.ExecuteNonQuery(ConnectionString(), spName, parameterValues);
        }

        #endregion ExecuteNonQuery

        #region ExecuteDataset

        /// <summary>
        /// 执行一个SQL命令，返回数据集
        /// </summary>
        /// <param name="commandType">命令类型</param>
        /// <param name="commandText">命令文本</param>
        /// <returns></returns>
        protected DataSet ExecuteDataset(CommandType commandType, string commandText)
        {
            return SqlHelper.ExecuteDataset(ConnectionString(), commandType, commandText);
        }

        /// <summary>
        /// 执行一个SQL命令，返回数据集
        /// </summary>
        /// <param name="commandType">命令类型</param>
        /// <param name="commandText">命令文本</param>
        /// <param name="commandParameters">命令参数列表</param>
        /// <returns></returns>
        protected DataSet ExecuteDataset(CommandType commandType, string commandText, params SqlParameter[] commandParameters)
        {
            return SqlHelper.ExecuteDataset(ConnectionString(), commandType, commandText, commandParameters);
        }

        /// <summary>
        /// 执行一个存储过程，返回数据集
        /// </summary>
        /// <param name="spName">存储过程</param>
        /// <param name="parameterValues">存储过程的参数值类表。注：是参数值列表，顺序和定义时的一样</param>
        /// <returns></returns>
        protected DataSet ExecuteDataset(string spName, params object[] parameterValues)
        {
            return SqlHelper.ExecuteDataset(ConnectionString(), spName, parameterValues);
        }

        #endregion ExecuteDataset

        #region ExecuteReader

        /// <summary>
        /// 执行一个SQL命令，返回只进读取器
        /// </summary>
        /// <param name="commandType">命令类型</param>
        /// <param name="commandText">命令文本</param>
        /// <returns></returns>
        protected SqlDataReader ExecuteReader(CommandType commandType, string commandText)
        {
            return SqlHelper.ExecuteReader(ConnectionString(), commandType, commandText);
        }

        /// <summary>
        /// 执行一个SQL命令，返回只进读取器
        /// </summary>
        /// <param name="commandType">命令类型</param>
        /// <param name="commandText">命令文本</param>
        /// <param name="commandParameters">命令参数列表</param>
        /// <returns></returns>
        protected SqlDataReader ExecuteReader(CommandType commandType, string commandText, params SqlParameter[] commandParameters)
        {
            return SqlHelper.ExecuteReader(ConnectionString(), commandType, commandText, commandParameters);
        }

        /// <summary>
        /// 执行一个存储过程，返回只进读取器
        /// </summary>
        /// <param name="spName">存储过程</param>
        /// <param name="parameterValues">存储过程的参数值类表。注：是参数值列表，顺序和定义时的一样</param>
        /// <returns></returns>
        protected SqlDataReader ExecuteReader(string spName, params object[] parameterValues)
        {
            return SqlHelper.ExecuteReader(ConnectionString(), spName, parameterValues);
        }

        #endregion ExecuteReader

        #region ExecuteScalar

        /// <summary>
        /// 执行一个SQL命令，返回首行首列的结果
        /// </summary>
        /// <param name="commandType">命令类型</param>
        /// <param name="commandText">命令文本</param>
        /// <returns></returns>
        protected object ExecuteScalar(CommandType commandType, string commandText)
        {
            return SqlHelper.ExecuteScalar(ConnectionString(), commandType, commandText);
        }

        /// <summary>
        /// 执行一个SQL命令，返回首行首列的结果
        /// </summary>
        /// <param name="commandType">命令类型</param>
        /// <param name="commandText">命令文本</param>
        /// <param name="commandParameters">命令参数列表</param>
        /// <returns></returns>
        protected object ExecuteScalar(CommandType commandType, string commandText, params SqlParameter[] commandParameters)
        {
            return SqlHelper.ExecuteScalar(ConnectionString(), commandType, commandText, commandParameters);
        }
        
        /// <summary>
        /// 执行一个存储过程，返回首行首列的结果
        /// </summary>
        /// <param name="spName">存储过程</param>
        /// <param name="parameterValues">存储过程的参数值类表。注：是参数值列表，顺序和定义时的一样</param>
        /// <returns></returns>
        protected object ExecuteScalar(string spName, params object[] parameterValues)
        {
            return SqlHelper.ExecuteScalar(ConnectionString(), spName, parameterValues);
        }

        #endregion ExecuteScalar

        #region Execute

        /// <summary>
        /// 执行一个SQL命令，映射到TModel集合并返回
        /// </summary>
        /// <param name="commandType">命令类型</param>
        /// <param name="commandText">命令文本</param>
        /// <returns></returns>
        protected List<TModel> ExecuteList(CommandType commandType, string commandText)
        {
            using (var reader = ExecuteReader(commandType, commandText))
            {
                return ReaderToModelList(reader);
            }
        }

        /// <summary>
        /// 执行一个SQL命令，映射到TModel集合并返回
        /// </summary>
        /// <param name="commandType">命令类型</param>
        /// <param name="commandText">命令文本</param>
        /// <param name="commandParameters">命令参数列表</param>
        /// <returns></returns>
        protected List<TModel> ExecuteList(CommandType commandType, string commandText, params SqlParameter[] commandParameters)
        {
            using (var reader = ExecuteReader(commandType, commandText, commandParameters))
            {
                return ReaderToModelList(reader);
            }
        }

        /// <summary>
        /// 执行一个存储过程，映射到TModel集合并返回
        /// </summary>
        /// <param name="spName">存储过程</param>
        /// <param name="parameterValues">存储过程的参数值类表。注：是参数值列表，顺序和定义时的一样</param>
        /// <returns></returns>
        protected List<TModel> ExecuteList(string spName, params object[] parameterValues)
        {
            using (var reader = ExecuteReader(spName, parameterValues))
            {
                return ReaderToModelList(reader);
            }
        }

        /// <summary>
        /// 执行一个SQL命令，映射到TModel并返回
        /// </summary>
        /// <param name="commandType">命令类型</param>
        /// <param name="commandText">命令文本</param>
        /// <returns></returns>
        protected TModel ExecuteOne(CommandType commandType, string commandText)
        {
            return ExecuteList(commandType, commandText).FirstOrDefault();
        }

        /// <summary>
        /// 执行一个SQL命令，映射到TModel并返回
        /// </summary>
        /// <param name="commandType">命令类型</param>
        /// <param name="commandText">命令文本</param>
        /// <param name="commandParameters">命令参数列表</param>
        /// <returns></returns>
        protected TModel ExecuteOne(CommandType commandType, string commandText, params SqlParameter[] commandParameters)
        {
            return ExecuteList(commandType, commandText, commandParameters).FirstOrDefault();
        }

        /// <summary>
        /// 执行一个存储过程，映射到TModel并返回
        /// </summary>
        /// <param name="spName">存储过程</param>
        /// <param name="parameterValues">存储过程的参数值类表。注：是参数值列表，顺序和定义时的一样</param>
        /// <returns></returns>
        protected TModel ExecuteOne(string spName, params object[] parameterValues)
        {
            return ExecuteList(spName, parameterValues).FirstOrDefault();
        }
        #endregion

        #region ExecuteDynamic

        /// <summary>
        /// 执行一个SQL命令，映射到dynamic集合并返回
        /// </summary>
        /// <param name="commandType">命令类型</param>
        /// <param name="commandText">命令文本</param>
        /// <returns></returns>
        protected List<dynamic> ExecuteDynamicList(CommandType commandType, string commandText)
        {
            using (var reader = ExecuteReader(commandType, commandText))
            {
                return ReaderToDynamicList(reader);
            }
        }

        /// <summary>
        /// 执行一个SQL命令，映射到dynamic集合并返回
        /// </summary>
        /// <param name="commandType">命令类型</param>
        /// <param name="commandText">命令文本</param>
        /// <param name="commandParameters">命令参数列表</param>
        /// <returns></returns>
        protected List<dynamic> ExecuteDynamicList(CommandType commandType, string commandText, params SqlParameter[] commandParameters)
        {
            using (var reader = ExecuteReader(commandType, commandText, commandParameters))
            {
                return ReaderToDynamicList(reader);
            }
        }

        /// <summary>
        /// 执行一个存储过程，映射到dynamic集合并返回
        /// </summary>
        /// <param name="spName">存储过程</param>
        /// <param name="parameterValues">存储过程的参数值类表。注：是参数值列表，顺序和定义时的一样</param>
        /// <returns></returns>
        protected List<dynamic> ExecuteDynamicList(string spName, params object[] parameterValues)
        {
            using (var reader = ExecuteReader(spName, parameterValues))
            {
                return ReaderToDynamicList(reader);
            }
        }

        /// <summary>
        /// 执行一个SQL命令，映射到dynamic并返回
        /// </summary>
        /// <param name="commandType">命令类型</param>
        /// <param name="commandText">命令文本</param>
        /// <returns></returns>
        protected dynamic ExecuteDynamicOne(CommandType commandType, string commandText)
        {
            return ExecuteDynamicList(commandType, commandText).FirstOrDefault();
        }

        /// <summary>
        /// 执行一个SQL命令，映射到dynamic并返回
        /// </summary>
        /// <param name="commandType">命令类型</param>
        /// <param name="commandText">命令文本</param>
        /// <param name="commandParameters">命令参数列表</param>
        /// <returns></returns>
        protected dynamic ExecuteDynamicOne(CommandType commandType, string commandText, params SqlParameter[] commandParameters)
        {
            return ExecuteDynamicList(commandType, commandText, commandParameters).FirstOrDefault();
        }

        /// <summary>
        /// 执行一个存储过程，映射到dynamic并返回
        /// </summary>
        /// <param name="spName">存储过程</param>
        /// <param name="parameterValues">存储过程的参数值类表。注：是参数值列表，顺序和定义时的一样</param>
        /// <returns></returns>
        protected dynamic ExecuteDynamicOne(string spName, params object[] parameterValues)
        {
            return ExecuteDynamicList(spName, parameterValues).FirstOrDefault();
        }
        #endregion

        #region ExecuteJson

        /// <summary>
        /// 执行一个SQL命令，返回JSON表示
        /// </summary>
        /// <param name="commandType">命令类型</param>
        /// <param name="commandText">命令文本</param>
        /// <returns></returns>
        protected StringBuilder ExecuteJsonList(CommandType commandType, string commandText)
        {
            using (var reader = ExecuteReader(commandType, commandText))
            {
                return ReaderToJson(reader);
            }
        }

        /// <summary>
        /// 执行一个SQL命令，返回JSON表示
        /// </summary>
        /// <param name="commandType">命令类型</param>
        /// <param name="commandText">命令文本</param>
        /// <param name="commandParameters">命令参数列表</param>
        /// <returns></returns>
        protected StringBuilder ExecuteJsonList(CommandType commandType, string commandText, params SqlParameter[] commandParameters)
        {
            using (var reader = ExecuteReader(commandType, commandText, commandParameters))
            {
                return ReaderToJson(reader);
            }
        }

        /// <summary>
        /// 执行一个存储过程，返回JSON表示
        /// </summary>
        /// <param name="spName">存储过程</param>
        /// <param name="parameterValues">存储过程的参数值类表。注：是参数值列表，顺序和定义时的一样</param>
        /// <returns></returns>
        protected StringBuilder ExecuteJsonList(string spName, params object[] parameterValues)
        {
            using (var reader = ExecuteReader(spName, parameterValues))
            {
                return ReaderToJson(reader);
            }
        }

        /// <summary>
        /// 执行一个SQL命令，返回JSON表示
        /// </summary>
        /// <param name="commandType">命令类型</param>
        /// <param name="commandText">命令文本</param>
        /// <returns></returns>
        protected StringBuilder ExecuteJsonOne(CommandType commandType, string commandText)
        {
            using (var reader = ExecuteReader(commandType, commandText))
            {
                 if (reader.Read())
                {
                    StringBuilder json = new StringBuilder();
                    ReaderToJson(reader, json);
                    return json;
                }
                 return new StringBuilder("null");
            }
        }

        /// <summary>
        /// 执行一个SQL命令，返回JSON表示
        /// </summary>
        /// <param name="commandType">命令类型</param>
        /// <param name="commandText">命令文本</param>
        /// <param name="commandParameters">命令参数列表</param>
        /// <returns></returns>
        protected StringBuilder ExecuteJsonOne(CommandType commandType, string commandText, params SqlParameter[] commandParameters)
        {
            using (var reader = ExecuteReader(commandType, commandText, commandParameters))
            {
                if (reader.Read())
                {
                    StringBuilder json = new StringBuilder();
                    ReaderToJson(reader, json);
                    return json;
                }
                return new StringBuilder("null");
            }
        }

        /// <summary>
        /// 执行一个存储过程，返回JSON表示
        /// </summary>
        /// <param name="spName">存储过程</param>
        /// <param name="parameterValues">存储过程的参数值类表。注：是参数值列表，顺序和定义时的一样</param>
        /// <returns></returns>
        protected StringBuilder ExecuteJsonOne(string spName, params object[] parameterValues)
        {
            using (var reader = ExecuteReader(spName, parameterValues))
            {
                if (reader.Read())
                {
                    StringBuilder json = new StringBuilder();
                    ReaderToJson(reader, json);
                    return json;
                }
                return new StringBuilder("null");
            }
        }

        #endregion

        #region General

        /// <summary>
        /// 添加模型对象到数据库，如果表有标识列，返回新的标识，如果没有，返回影响行数
        /// </summary>
        /// <param name="model">模型对象</param>
        /// <returns></returns>
        public int Add(TModel model)
        {
            var type = typeof(TModel);
            var table = type.GetTableName();
            var propertys = type.GetProperties_Cache(System.Reflection.BindingFlags.Public | System.Reflection.BindingFlags.Instance);

            StringBuilder sbsql = new StringBuilder();
            sbsql.AppendFormat("insert into [{0}] (", table);
            StringBuilder sbParams = new StringBuilder(") values (");
            var ps = new List<SqlParameter>();
            foreach (var property in propertys)
            {
                var fieldAttr = (DBFieldAttribute)property.GetCustomAttributes_Cache(typeof(DBFieldAttribute), false).FirstOrDefault();
                if (fieldAttr == null || (!fieldAttr.InsertIgnore && !fieldAttr.Ignore))
                {
                    var fieldName = property.Name;
                    if (fieldAttr != null && fieldAttr.Name != null && fieldAttr.Name.Length > 0)
                    {
                        fieldName = fieldAttr.Name;
                    }
                    var val = property.GetValue(model, null);
                    if (val == null)
                    {
                        if (property.PropertyType == typeof(string))
                        {
                            val = string.Empty;
                        }
                    }
                    sbsql.AppendFormat("[{0}],", fieldName);
                    sbParams.AppendFormat("@{0},", fieldName);
                    ps.Add(new SqlParameter("@" + fieldName, val ?? DBNull.Value));
                }
            }
            if (ps.Count == 0)
            {
                throw new ArgumentException("model里没有字段，无法插入");
            }
            sbsql.Remove(sbsql.Length - 1, 1);
            sbParams.Remove(sbParams.Length - 1, 1);
            sbsql.Append(sbParams.ToString());
            sbsql.Append(")");

            //返回自增id
            return Add(sbsql.ToString(), ps.ToArray());
        }

        //返回自增id的add方法
        private int Add(string sql, params SqlParameter[] ps)
        {
            using (var conn = NewConnection())
            {
                var obj = SqlHelper.ExecuteScalar(conn, CommandType.Text, sql + ";SELECT ISNULL(SCOPE_IDENTITY(),@@rowcount)", ps);
                if (obj == null || obj is DBNull)
                {
                    return 0;
                }
                else
                {
                    return Convert.ToInt32(obj);
                }
            }
        }

        /// <summary>
        /// 添加指定字段到数据库，如果表有标识列，返回新的标识，如果没有，返回影响行数
        /// </summary>
        /// <param name="fields">指定字段</param>
        /// <returns></returns>
        public int Add(IDictionary<string, object> fields)
        {
            var type = typeof(TModel);
            var table = type.GetTableName();

            StringBuilder sbsql = new StringBuilder();
            sbsql.AppendFormat("insert into [{0}] (", table);
            StringBuilder sbParams = new StringBuilder(") values (");
            var ps = new List<SqlParameter>();
            foreach (var field in fields)
            {
                sbsql.AppendFormat("[{0}],", field.Key);
                sbParams.AppendFormat("@{0},", field.Key);
                ps.Add(new SqlParameter("@" + field.Key, field.Value));
            }
            if (ps.Count == 0)
            {
                throw new ArgumentException("model里没有字段，无法插入");
            }
            sbsql.Remove(sbsql.Length - 1, 1);
            sbParams.Remove(sbParams.Length - 1, 1);
            sbsql.Append(sbParams.ToString());
            sbsql.Append(")");

            //返回自增id
            return Add(sbsql.ToString(), ps.ToArray());
        }

        /// <summary>
        /// 删除指定条件的数据
        /// </summary>
        /// <param name="conditions">删除条件</param>
        /// <param name="ps">条件里的参数列表</param>
        /// <returns></returns>
        public int Delete(string conditions=null, params SqlParameter[] ps)
        {
            var type = typeof(TModel);
            var table = type.GetTableName();
            string sql;
            if (string.IsNullOrEmpty(conditions))
            {
                sql = "delete from [" + table + "]";
            }
            else
            {
                sql = "delete from [" + table + "] where " + conditions;
            }
            int c = ExecuteNonQuery(CommandType.Text, sql, ps);
            return c;
        }

        /// <summary>
        /// 根据TModel对象的PrimaryKey属性修改整个模型对象
        /// </summary>
        /// <param name="model">模型对象</param>
        /// <returns></returns>
        public int Update(TModel model)
        {
            var type = typeof(TModel);
            var table = type.GetTableName();
            var propertys = type.GetProperties_Cache(System.Reflection.BindingFlags.Public | System.Reflection.BindingFlags.Instance);
            StringBuilder sbsql = new StringBuilder();
            sbsql.AppendFormat("update [{0}] set ", table);
            string condition = null;
            var ps = new List<SqlParameter>();
            foreach (var property in propertys)
            {
                var fieldAttr = (DBFieldAttribute)property.GetCustomAttributes_Cache(typeof(DBFieldAttribute), false).FirstOrDefault();
                if (fieldAttr == null || (!fieldAttr.UpdateIgnore && !fieldAttr.Ignore))
                {
                    var fieldName = property.Name;
                    if (fieldAttr != null && fieldAttr.Name != null && fieldAttr.Name.Length > 0)
                    {
                        fieldName = fieldAttr.Name;
                    }

                    var val = property.GetValue(model, null);
                    if (val == null)
                    {
                        if (property.PropertyType == typeof(string))
                        {
                            val = string.Empty;
                        }
                    }
                    if (fieldAttr != null && fieldAttr.IsPrimaryKey)
                    {
                        condition = string.Format("[{0}] = @{0}", fieldName);
                    }
                    else
                    {
                        sbsql.AppendFormat("[{0}] = @{0},", fieldName);
                    }
                    ps.Add(new SqlParameter("@" + fieldName, val ?? DBNull.Value));
                }
            }
            if (ps.Count == 0)
            {
                throw new ArgumentException("model里没有字段，无法修改");
            }
            sbsql.Remove(sbsql.Length - 1, 1);
            sbsql.AppendFormat(" where ");
            sbsql.Append(condition);
            int c = ExecuteNonQuery(CommandType.Text, sbsql.ToString(), ps.ToArray());
            return c;
        }

        /// <summary>
        /// 根据指定条件修改表的指定字段
        /// </summary>
        /// <param name="updateFields">指定要修改的字段</param>
        /// <param name="conditions">修改条件</param>
        /// <param name="conditionPs">修改条件里的参数列表</param>
        /// <returns></returns>
        public int Update(IDictionary<string, object> updateFields, string conditions, params SqlParameter[] conditionPs)
        {
            var type = typeof(TModel);
            var table = type.GetTableName();

            StringBuilder sbsql = new StringBuilder();
            sbsql.AppendFormat("update [{0}] set ", table);
            var ps = new List<SqlParameter>();
            var nofield = true;
            foreach (var field in updateFields)
            {
                if (field.Key.StartsWith("$") || field.Key.StartsWith("_"))
                {
                    sbsql.AppendFormat("[{0}] = {1},", field.Key.Substring(1), field.Value);
                    nofield = false;
                }
                else
                {
                    sbsql.AppendFormat("[{0}] = @_{0},", field.Key);
                    ps.Add(new SqlParameter("@_" + field.Key, field.Value));
                }
            }
            if (ps.Count == 0 && nofield)
            {
                throw new ArgumentException("updateFields里没有字段，无法修改");
            }
            sbsql.Remove(sbsql.Length - 1, 1);
            sbsql.AppendFormat(" where {0}", conditions);
            ps.AddRange(conditionPs);
            int c = ExecuteNonQuery(CommandType.Text, sbsql.ToString(), ps.ToArray());
            return c;
        }

        /// <summary>
        /// 根据指定条件修改表的指定字段
        /// </summary>
        /// <param name="updateBuilder">修改的字段包装器</param>
        /// <param name="conditions">修改条件</param>
        /// <param name="conditionPs">修改条件里的参数列表</param>
        /// <returns></returns>
        public int Update(UpdateBuilder updateBuilder, string conditions, params SqlParameter[] conditionPs)
        {
            return Update(updateBuilder.SetDict, conditions, conditionPs);
        }

        /// <summary>
        /// 查询所有数据
        /// </summary>
        /// <param name="fields">指定字段</param>
        /// <param name="orderby">指定排序</param>
        /// <returns></returns>
        public List<TModel> FindAll(string fields = null, string orderby = null)
        {
            var table = typeof(TModel).GetTableName();
            var sql = "select * from [" + table + "]";
            if (fields != null && fields.Length > 0)
            {
                sql = "select " + fields + " from [" + table + "]";
            }
            if (orderby != null && orderby.Length > 0)
            {
                sql += " order by " + orderby;
            }
            return ExecuteList(CommandType.Text, sql);
        }

        /// <summary>
        /// 查询列表数据
        /// </summary>
        /// <param name="conditions">查询条件</param>
        /// <param name="fields">指定字段</param>
        /// <param name="top">指定top数量</param>
        /// <param name="orderby">指定排序</param>
        /// <param name="conditionPs">查询条件里的参数列表</param>
        /// <returns></returns>
        public List<TModel> FindList(string conditions,string fields=null, int top=0,  string orderby=null, params SqlParameter[] conditionPs)
        {
            var type = typeof(TModel);
            var table = type.GetTableName();
            StringBuilder sb = new StringBuilder();
            if (string.IsNullOrEmpty(fields))
            {
                fields = "*";
            }
            if (top > 0)
            {
                sb.AppendFormat("select top {0} {1} from [{2}]", top, fields, table);
            }
            else
            {
                sb.AppendFormat("select {0} from [{1}]", fields, table);
            }
            if (!string.IsNullOrEmpty(conditions))
            {
                sb.AppendFormat(" where {0}", conditions);
            }
            if (!string.IsNullOrEmpty(orderby))
            {
                sb.AppendFormat(" order by {0}", orderby);
            }
            var list = ExecuteList(CommandType.Text, sb.ToString(), conditionPs);
            return list;
        }

        /// <summary>
        /// 查询单条数据
        /// </summary>
        /// <param name="conditions">查询条件</param>
        /// <param name="fields">指定字段</param>
        /// <param name="orderby">指定排序</param>
        /// <param name="conditionPs">查询条件里的参数列表</param>
        /// <returns></returns>
        public TModel FindOne(string conditions, string fields=null, string orderby=null, params SqlParameter[] conditionPs)
        {
            return FindList(conditions, fields,1, orderby, conditionPs).FirstOrDefault();
        }

        /// <summary>
        /// 查询分页数据
        /// </summary>
        /// <param name="conditions">查询条件</param>
        /// <param name="fields">指定字段</param>
        /// <param name="orderby">指定排序</param>
        /// <param name="pageIndex">页码</param>
        /// <param name="pageSize">每页条数</param>
        /// <param name="isTotalCount">是否查询总数</param>
        /// <param name="conditionPs">查询条件里的参数列表</param>
        /// <returns></returns>
        public PageResult<TModel> FindPage(string conditions, string fields, string orderby, int pageIndex, int pageSize, bool isTotalCount, params SqlParameter[] conditionPs)
        {
            var type = typeof(TModel);
            var table = type.GetTableName();
            if (pageIndex < 1)
            {
                throw new ArgumentException("pageIndex参数应>1");
            }
            if (string.IsNullOrEmpty(orderby))
            {
                throw new ArgumentException("orderby参数不能为空或null");
            }
            if (string.IsNullOrEmpty(fields))
            {
                fields = "*";
            }
            PageResult<TModel> result = new PageResult<TModel>();
            
            StringBuilder sb = new StringBuilder();
            if (isTotalCount)
            {
                sb.AppendFormat("select count(0) from [{0}]", table);
                if (!string.IsNullOrEmpty(conditions))
                {
                    sb.AppendFormat(" where {0}", conditions);
                }
                result.TotalCount = (int)ExecuteScalar(CommandType.Text, sb.ToString(), conditionPs);
                if (result.TotalCount == 0) //总数为0了，肯定没有数据
                {
                    result.DataList = new List<TModel>();
                    return result;
                }
                sb.Clear();
            }
            sb.AppendFormat("select * from (");
            sb.AppendFormat(" select top {0} {1},ROW_NUMBER() over(order by {2}) rowid from [{3}]", pageIndex * pageSize, fields, orderby, table);
            if (!string.IsNullOrEmpty(conditions))
            {
                sb.AppendFormat(" where {0}", conditions);
            }
            sb.AppendFormat(")t where t.rowid>{0}", (pageIndex - 1) * pageSize);
            result.DataList = ExecuteList(CommandType.Text, sb.ToString(), CopyParameters(conditionPs));
            result.PageIndex = pageIndex;
            result.PageSize = pageSize;

            return result;
        }

        #endregion

        #region Aggregation

        private T GetAggregation<T>(string fun, string field, string conditions, params SqlParameter[] ps)
        {
            var type = typeof(TModel);
            var table = type.GetTableName();
            string sql;
            if (string.IsNullOrEmpty(conditions))
            {
                sql = "select " + fun + "(" + field + ") from [" + table + "]";
            }
            else
            {
                sql = "select " + fun + "(" + field + ") from [" + table + "] where " + conditions;
            }
            T c = (T)ExecuteScalar(CommandType.Text, sql, ps);
            return c;
        }

        /// <summary>
        /// 根据条件统计数量
        /// </summary>
        /// <param name="conditions">查询条件</param>
        /// <param name="ps">查询条件里的参数列表</param>
        /// <returns></returns>
        public int GetCount(string conditions = null, params SqlParameter[] ps)
        {
            return GetAggregation<int>("count", "0", conditions, ps);
        }

        /// <summary>
        /// 根据条件统计最大值
        /// </summary>
        /// <typeparam name="T">统计结果类型</typeparam>
        /// <param name="field">统计字段</param>
        /// <param name="conditions">查询条件</param>
        /// <param name="ps">查询条件里的参数列表</param>
        /// <returns></returns>
        public T GetMax<T>(string field, string conditions = null, params SqlParameter[] ps)
        {
            return GetAggregation<T>("max", field, conditions, ps);
        }

        /// <summary>
        /// 根据条件统计最小值
        /// </summary>
        /// <typeparam name="T">统计结果类型</typeparam>
        /// <param name="field">统计字段</param>
        /// <param name="conditions">查询条件</param>
        /// <param name="ps">查询条件里的参数列表</param>
        /// <returns></returns>
        public T GetMin<T>(string field, string conditions = null, params SqlParameter[] ps)
        {
            return GetAggregation<T>("min", field, conditions, ps);
        }

        /// <summary>
        /// 根据条件统计平均值
        /// </summary>
        /// <typeparam name="T">统计结果类型</typeparam>
        /// <param name="field">统计字段</param>
        /// <param name="conditions">查询条件</param>
        /// <param name="ps">查询条件里的参数列表</param>
        /// <returns></returns>
        public T GetAvg<T>(string field, string conditions = null, params SqlParameter[] ps)
        {
            return GetAggregation<T>("avg", field, conditions, ps);
        }

        /// <summary>
        /// 根据条件统计总和
        /// </summary>
        /// <typeparam name="T">统计结果类型</typeparam>
        /// <param name="field">统计字段</param>
        /// <param name="conditions">查询条件</param>
        /// <param name="ps">查询条件里的参数列表</param>
        /// <returns></returns>
        public T GetSum<T>(string field, string conditions = null, params SqlParameter[] ps)
        {
            return GetAggregation<T>("sum", field, conditions, ps);
        }

        #endregion

        #region Helper

        /// <summary>
        /// 复制参数数组
        /// </summary>
        /// <param name="parames"></param>
        /// <returns></returns>
        public SqlParameter[] CopyParameters(params SqlParameter[] parames)
        {
            SqlParameter[] ps = new SqlParameter[parames.Length];
            for (int i = 0; i < parames.Length; i++)
            {
                ps[i] = (SqlParameter)((ICloneable)parames[i]).Clone();
            }
            return ps;
        }

        /// <summary>
        /// 创建命令参数
        /// </summary>
        /// <param name="name">参数名</param>
        /// <param name="value">参数值</param>
        /// <returns></returns>
        public SqlParameter CreateParameter(string name, object value)
        {
            return new SqlParameter(name, value);
        }

        /// <summary>
        /// 创建命令参数
        /// </summary>
        /// <param name="name">参数名</param>
        /// <param name="dbType">参数类型</param>
        /// <param name="direction">参数方向</param>
        /// <returns></returns>
        public SqlParameter CreateParameter(string name, SqlDbType dbType, ParameterDirection direction)
        {
            var p = new SqlParameter(name, dbType);
            p.Direction = direction;
            return p;
        }

        
        /// <summary>
        /// 返回为某个会话和当前作用域中的指定表生成的最新标识值。
        /// </summary>
        /// <returns></returns>
        public int IDENT_CURRENT(string tableName = null)
        {
            if (string.IsNullOrEmpty(tableName))
            {
                var type = typeof(TModel);
                tableName = type.GetTableName();
            }

            var obj = ExecuteScalar(CommandType.Text, " SELECT IDENT_CURRENT('[" + tableName + "]')");
            if (obj == null || obj is DBNull)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }

        #endregion
    }
}
